package org.compiere.apps.form;

import java.awt.BorderLayout;
import java.awt.Component;
import java.awt.Dimension;
import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.awt.event.ActionEvent;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Vector;
import java.util.logging.Level;

import javax.swing.BorderFactory;
import javax.swing.JLabel;
import javax.swing.JSplitPane;
import javax.swing.SwingConstants;

import org.adempiere.plaf.AdempierePLAF;
import org.compiere.grid.ed.VLookup;
import org.compiere.minigrid.IMiniTable;
import org.compiere.model.MLookup;
import org.compiere.model.MLookupFactory;
import org.compiere.model.MRole;
import org.compiere.plaf.CompiereColor;
import org.compiere.swing.CPanel;
import org.compiere.util.DB;
import org.compiere.util.DisplayType;
import org.compiere.util.Env;
import org.compiere.util.KeyNamePair;
import org.compiere.util.Msg;
import org.compiere.util.Util;

public class TAllocation extends VAllocation {

	protected VLookup cms_Contract = null;
	protected JLabel cms_ContractLabel = new JLabel();
	
	@Override
	public void init (int WindowNo, FormFrame frame) {
		Env.setContext(Env.getCtx(),WindowNo, "C_BPartner_ID", 0);
		Env.setContext(Env.getCtx(),WindowNo, "CMS_Contract_ID", 0);
		
		m_WindowNo = WindowNo;
		m_frame = frame;
		Env.setContext(Env.getCtx(), m_WindowNo, "IsSOTrx", "Y");   //  defaults to no
		try
		{
			super.init(WindowNo, frame);
			
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, "", e);
		}
	}	//	init
	
	@Override
	protected void jbInit() throws Exception {
		super.jbInit();
		parameterPanel.add(cms_ContractLabel, new GridBagConstraints(4, 2, 1, 1, 0.0, 0.0,GridBagConstraints.EAST, GridBagConstraints.NONE, new Insets(5, 5, 5, 5), 0, 0));
		parameterPanel.add(cms_Contract, new GridBagConstraints(5, 2, 1, 1, 0.0, 0.0,GridBagConstraints.WEST, GridBagConstraints.NONE, new Insets(5, 0, 5, 5), 0, 0));
		cms_ContractLabel.setText(Msg.translate(Env.getCtx(), "CMS_Contract_ID"));
		
//		bpartnerSearch.addActionListener(this);
		cms_Contract.addActionListener(this);
		
		
	}   //  jbInit
	
	@Override
	public void dynInit() throws Exception {
		super.dynInit();
		int AD_Column_ID = getColumnID("CMS_Contract_ID");        //  C_Invoice.C_BPartner_ID
		int AD_Reference_ID = getReferenceID("Table");
		int AD_Reference_Value_ID = getReferenceValueID("ContractBP");
		MLookup lookupcms_Contract = MLookupFactory.get (Env.getCtx(), m_WindowNo, AD_Column_ID, AD_Reference_ID, Env.getLanguage(Env.getCtx()), "CMS_Contract_Contractnum", AD_Reference_Value_ID, true, "CMS_Contract.C_BPartner_ID = @C_BPartner_ID@");
		cms_Contract = new VLookup("cms_Contract_ID", false, false, true, lookupcms_Contract);
		cms_Contract.addVetoableChangeListener(this);
	}   //  dynInit
	
	protected int getReferenceID(String name) {
		int result = 0;
		String sql = "select AD_Reference_ID from AD_Reference where Name like ?";
		
		log.fine("SQL=" + sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setString(1,name);
		    rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		}
		catch (SQLException e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; 
			pstmt = null;
		}
		return result;
	}
	
	protected int getReferenceValueID(String name) {
		int result = 0;
		String sql = "select AD_Reference_ID from AD_Reference where Name like ?";
		
		log.fine("SQL=" + sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setString(1,name);
		    rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		}
		catch (SQLException e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; 
			pstmt = null;
		}
		return result;
	}
	
	protected int getColumnID(String columnname) {
		int result = 0;
		String sql = "select AD_Column_ID from AD_Column where ColumnName like ?";
		
		log.fine("SQL=" + sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setString(1,columnname);
		    rs = pstmt.executeQuery();
			while (rs.next()) {
				result = rs.getInt(1);
			}
		}
		catch (SQLException e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; 
			pstmt = null;
		}
		return result;
	}
	
	public void loadBPartner() {
		super.loadBPartner();
		Env.setContext(Env.getCtx(),m_WindowNo, "C_BPartner_ID", (Integer)bpartnerSearch.getValue());
		
		cms_Contract.refresh();
//		cms_Contract.setValue(getCMS_Contract_ID(m_C_BPartner_ID));
	}
	
	protected int getCMS_Contract_ID(int C_BPartner_ID) {
		int result = 0;
		String sql = "select CMS_Contract_ID from CMS_Contract where C_BPartner_ID = ?";
		
		log.fine("SQL=" + sql);
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = DB.prepareStatement(sql.toString(), null);
			pstmt.setInt(1,C_BPartner_ID);
		    rs = pstmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		}
		catch (SQLException e) {
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally {
			DB.close(rs, pstmt);
			rs = null; 
			pstmt = null;
		}
		
		return result;
	}
	
	@Override
	public Vector<Vector<Object>> getInvoiceData(boolean isMultiCurrency, Object date, IMiniTable invoiceTable)
	{
		/********************************
		 *  Load unpaid Invoices
		 *      1-TrxDate, 2-Value, (3-Currency, 4-InvAmt,)
		 *      5-ConvAmt, 6-ConvOpen, 7-ConvDisc, 8-WriteOff, 9-Applied, 10 - Contract
		 * 
		 SELECT i.DateInvoiced,i.DocumentNo,i.C_Invoice_ID,c.ISO_Code,
		 i.GrandTotal*i.MultiplierAP "GrandTotal", 
		 currencyConvert(i.GrandTotal*i.MultiplierAP,i.C_Currency_ID,i.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID) "GrandTotal $", 
		 invoiceOpen(C_Invoice_ID,C_InvoicePaySchedule_ID) "Open",
		 currencyConvert(invoiceOpen(C_Invoice_ID,C_InvoicePaySchedule_ID),i.C_Currency_ID,i.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*i.MultiplierAP "Open $", 
		 invoiceDiscount(i.C_Invoice_ID,SysDate,C_InvoicePaySchedule_ID) "Discount",
		 currencyConvert(invoiceDiscount(i.C_Invoice_ID,SysDate,C_InvoicePaySchedule_ID),i.C_Currency_ID,i.C_Currency_ID,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*i.Multiplier*i.MultiplierAP "Discount $",
		 i.MultiplierAP, i.Multiplier 
		 FROM C_Invoice_v i INNER JOIN C_Currency c ON (i.C_Currency_ID=c.C_Currency_ID)
		 INNER JOIN CMS_Contract cms on (cms.CMS_Contract_ID = i.CMS_Contract_ID) 
		 WHERE -- i.IsPaid='N' AND i.Processed='Y' AND i.C_BPartner_ID=1000001
		 */
		Vector<Vector<Object>> data = new Vector<Vector<Object>>();
		StringBuffer sql = new StringBuffer("SELECT i.DateInvoiced,i.DocumentNo,i.C_Invoice_ID," //  1..3
			+ "c.ISO_Code,i.GrandTotal*i.MultiplierAP, "                            //  4..5    Orig Currency
			+ "currencyConvert(i.GrandTotal*i.MultiplierAP,i.C_Currency_ID,?,?,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID), " //  6   #1  Converted, #2 Date
			+ "currencyConvert(invoiceOpen(C_Invoice_ID,C_InvoicePaySchedule_ID),i.C_Currency_ID,?,?,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*i.MultiplierAP, "  //  7   #3, #4  Converted Open
			+ "currencyConvert(invoiceDiscount"                               //  8       AllowedDiscount
			+ "(i.C_Invoice_ID,?,C_InvoicePaySchedule_ID),i.C_Currency_ID,?,i.DateInvoiced,i.C_ConversionType_ID,i.AD_Client_ID,i.AD_Org_ID)*i.Multiplier*i.MultiplierAP,"               //  #5, #6
			+ "i.MultiplierAP " // 10
			+ ", cms.cms_contract_contractnum as Contract  " // 11
			+ "FROM C_Invoice_v i"		//  corrected for CM/Split
			+ " INNER JOIN C_Currency c ON (i.C_Currency_ID=c.C_Currency_ID) "
			+ " INNER JOIN CMS_Contract cms on (cms.CMS_Contract_ID in (select CMS_Contract_ID from C_Invoice ci where ci.C_Invoice_ID = i.C_Invoice_ID)) "
			+ "WHERE i.IsPaid='N' AND i.Processed='Y'"
			+ " AND i.C_BPartner_ID=?"    // #7
			+ " AND (cms.CMS_Contract_ID = ? or ? = 0)");                                            //  #8 , 9
		if (!isMultiCurrency)
			sql.append(" AND i.C_Currency_ID=?");                                   //  #10
		if (m_AD_Org_ID != 0 ) 
			sql.append(" AND i.AD_Org_ID=" + m_AD_Org_ID);
		sql.append(" ORDER BY i.DateInvoiced, i.DocumentNo");
		log.fine("InvSQL=" + sql.toString());
		
		// role security
//		sql = new StringBuffer( MRole.getDefault(Env.getCtx(), false).addAccessSQL( sql.toString(), "i", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO ) );
		
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
			
			int CMS_Contract_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "CMS_Contract_ID");
			pstmt.setInt(1, m_C_Currency_ID);
			pstmt.setTimestamp(2, (Timestamp)date);
			pstmt.setInt(3, m_C_Currency_ID);
			pstmt.setTimestamp(4, (Timestamp)date);
			pstmt.setTimestamp(5, (Timestamp)date);
			pstmt.setInt(6, m_C_Currency_ID);
			pstmt.setInt(7, m_C_BPartner_ID);
			
			pstmt.setInt(8, CMS_Contract_ID);
			pstmt.setInt(9, CMS_Contract_ID);
			if (!isMultiCurrency)
				pstmt.setInt(10, m_C_Currency_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				Vector<Object> line = new Vector<Object>();
				line.add(new Boolean(false));       //  0-Selection
				line.add(rs.getTimestamp(1));       //  1-TrxDate
				KeyNamePair pp = new KeyNamePair(rs.getInt(3), rs.getString(2));
				line.add(pp);                       //  2-Value
				if (isMultiCurrency)
				{
					line.add(rs.getString(4));      //  3-Currency
					line.add(rs.getBigDecimal(5));  //  4-Orig Amount
				}
				line.add(rs.getBigDecimal(6));      //  3/5-ConvAmt
				BigDecimal open = rs.getBigDecimal(7);
				if (open == null)		//	no conversion rate
					open = Env.ZERO;
				line.add(open);      				//  4/6-ConvOpen
				BigDecimal discount = rs.getBigDecimal(8);
				if (discount == null)	//	no concersion rate
					discount = Env.ZERO;
				line.add(discount);					//  5/7-ConvAllowedDisc
				line.add(Env.ZERO);      			//  6/8-WriteOff
				line.add(Env.ZERO);					// 7/9-Applied
				line.add(open);				    //  8/10-OverUnder
				line.add(rs.getString(10));

				if (Env.ZERO.compareTo(open) != 0)
					data.add(line);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		
		return data;
	}
	
	@Override
	public Vector<String> getInvoiceColumnNames(boolean isMultiCurrency)
	{
		//  Header Info
		Vector<String> columnNames = new Vector<String>();
		columnNames.add(Msg.getMsg(Env.getCtx(), "Select"));
		columnNames.add(Msg.translate(Env.getCtx(), "Date"));
		columnNames.add(Util.cleanAmp(Msg.translate(Env.getCtx(), "DocumentNo")));
		if (isMultiCurrency)
		{
			columnNames.add(Msg.getMsg(Env.getCtx(), "TrxCurrency"));
			columnNames.add(Msg.translate(Env.getCtx(), "Amount"));
		}
		columnNames.add(Msg.getMsg(Env.getCtx(), "ConvertedAmount"));
		columnNames.add(Msg.getMsg(Env.getCtx(), "OpenAmt"));
		columnNames.add(Msg.getMsg(Env.getCtx(), "Discount"));
		columnNames.add(Msg.getMsg(Env.getCtx(), "WriteOff"));
		columnNames.add(Msg.getMsg(Env.getCtx(), "AppliedAmt"));
		columnNames.add(Msg.getMsg(Env.getCtx(), "OverUnderAmt"));
		columnNames.add(Msg.translate(Env.getCtx(), "CMS_Contract_ID"));
		
		
		return columnNames;
	}
	
	@Override
	public void setInvoiceColumnClass(IMiniTable invoiceTable, boolean isMultiCurrency)
	{
		int i = 0;
		invoiceTable.setColumnClass(i++, Boolean.class, false);         //  0-Selection
		invoiceTable.setColumnClass(i++, Timestamp.class, true);        //  1-TrxDate
		invoiceTable.setColumnClass(i++, String.class, true);           //  2-Value
		if (isMultiCurrency)
		{
			invoiceTable.setColumnClass(i++, String.class, true);       //  3-Currency
			invoiceTable.setColumnClass(i++, BigDecimal.class, true);   //  4-Amt
		}
		invoiceTable.setColumnClass(i++, BigDecimal.class, true);       //  5-ConvAmt
		invoiceTable.setColumnClass(i++, BigDecimal.class, true);       //  6-ConvAmt Open
		invoiceTable.setColumnClass(i++, BigDecimal.class, false);      //  7-Conv Discount
		invoiceTable.setColumnClass(i++, BigDecimal.class, false);      //  8-Conv WriteOff
		invoiceTable.setColumnClass(i++, BigDecimal.class, false);      //  9-Conv OverUnder
		invoiceTable.setColumnClass(i++, BigDecimal.class, true);		//	10-Conv Applied
		invoiceTable.setColumnClass(i++, String.class, true);		//	11-Conv Applied

		invoiceTable.autoSize();
	}
	
	@Override
	public Vector<Vector<Object>> getPaymentData(boolean isMultiCurrency, Object date, IMiniTable paymentTable)
	{		
		/********************************
		 *  Load unallocated Payments
		 *      1-TrxDate, 2-DocumentNo, (3-Currency, 4-PayAmt,)
		 *      5-ConvAmt, 6-ConvOpen, 7-Allocated, 8 - Contract
		 */
		Vector<Vector<Object>> data = new Vector<Vector<Object>>();
		StringBuffer sql = new StringBuffer("SELECT p.DateTrx,p.DocumentNo,p.C_Payment_ID,"  //  1..3
			+ "c.ISO_Code,p.PayAmt,"                            //  4..5
			+ "currencyConvert(p.PayAmt,p.C_Currency_ID,?,?,p.C_ConversionType_ID,p.AD_Client_ID,p.AD_Org_ID),"//  6   #1, #2
			+ "currencyConvert(paymentAvailable(C_Payment_ID),p.C_Currency_ID,?,?,p.C_ConversionType_ID,p.AD_Client_ID,p.AD_Org_ID),"  //  7   #3, #4
			+ "p.MultiplierAP " // 8
			+ ", cms.cms_contract_contractnum as Contract  " // 9
			+ "FROM C_Payment_v p"		//	Corrected for AP/AR
			+ " INNER JOIN C_Currency c ON (p.C_Currency_ID=c.C_Currency_ID) "
			+ " INNER JOIN CMS_Contract cms on (cms.CMS_Contract_ID in (select CMS_Contract_ID from C_Payment cp where cp.C_Payment_ID = p.C_Payment_ID)) "
			+ "WHERE p.IsAllocated='N' AND p.Processed='Y'"
			+ " AND p.C_Charge_ID IS NULL"		//	Prepayments OK
			+ " AND p.C_BPartner_ID=?"  // #5
			+ " AND (cms.CMS_Contract_ID = ? or ? = 0)");                   		//      #6,7
		if (!isMultiCurrency)
			sql.append(" AND p.C_Currency_ID=?");				//      #6
		if (m_AD_Org_ID != 0 )
			sql.append(" AND p.AD_Org_ID=" + m_AD_Org_ID);
		sql.append(" ORDER BY p.DateTrx,p.DocumentNo");
		
		// role security
//		sql = new StringBuffer( MRole.getDefault(Env.getCtx(), false).addAccessSQL( sql.toString(), "p", MRole.SQL_FULLYQUALIFIED, MRole.SQL_RO ) );
		
		log.fine("PaySQL=" + sql.toString());
		try
		{
			PreparedStatement pstmt = DB.prepareStatement(sql.toString(), null);
			int CMS_Contract_ID = Env.getContextAsInt(Env.getCtx(), m_WindowNo, "CMS_Contract_ID");
			pstmt.setInt(1, m_C_Currency_ID);
			pstmt.setTimestamp(2, (Timestamp)date);
			pstmt.setInt(3, m_C_Currency_ID);
			pstmt.setTimestamp(4, (Timestamp)date);
			pstmt.setInt(5, m_C_BPartner_ID);
			pstmt.setInt(6, CMS_Contract_ID);
			pstmt.setInt(7, CMS_Contract_ID);
			if (!isMultiCurrency)
				pstmt.setInt(8, m_C_Currency_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next())
			{
				Vector<Object> line = new Vector<Object>();
				line.add(new Boolean(false));       //  0-Selection				// 0
				line.add(rs.getTimestamp(1));       //  1-TrxDate				// 1
				KeyNamePair pp = new KeyNamePair(rs.getInt(3), rs.getString(2));
				line.add(pp);                       //  2-DocumentNo			// 2
				if (isMultiCurrency)
				{
					line.add(rs.getString(4));      //  3-Currency				// 3
					line.add(rs.getBigDecimal(5));  //  4-PayAmt				// 4
				}
				line.add(rs.getBigDecimal(6));      //  3/5-ConvAmt 			// 5
				BigDecimal available = rs.getBigDecimal(7);
				if (available == null || available.signum() == 0)	//	nothing available
					continue;
				line.add(available);				//  4/6-ConvOpen/Available	// 6
				line.add(Env.ZERO);					//  5/7-Payment				// 7
				
				line.add(rs.getString(9));										// 8
				
				//line.add(rs.getBigDecimal(8));		//  6/8-Multiplier			// 9
				//
				data.add(line);
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, sql.toString(), e);
		}
		
		return data;
	}
	
	@Override
	public Vector<String> getPaymentColumnNames(boolean isMultiCurrency)
	{	
		//  Header Info
		Vector<String> columnNames = new Vector<String>();
		columnNames.add(Msg.getMsg(Env.getCtx(), "Select"));							// 0
		columnNames.add(Msg.translate(Env.getCtx(), "Date"));							// 1
		columnNames.add(Util.cleanAmp(Msg.translate(Env.getCtx(), "DocumentNo")));		// 2
		if (isMultiCurrency)		
		{
			columnNames.add(Msg.getMsg(Env.getCtx(), "TrxCurrency"));					// 3
			columnNames.add(Msg.translate(Env.getCtx(), "Amount"));						// 4
		}
		columnNames.add(Msg.getMsg(Env.getCtx(), "ConvertedAmount"));					// 5
		columnNames.add(Msg.getMsg(Env.getCtx(), "OpenAmt"));							// 6
		columnNames.add(Msg.getMsg(Env.getCtx(), "AppliedAmt"));						// 7
		columnNames.add(Msg.translate(Env.getCtx(), "CMS_Contract_ID"));				// 8
		//columnNames.add(" ");	//	Multiplier											// 9
		
		return columnNames;
	}
	
	@Override
	public void setPaymentColumnClass(IMiniTable paymentTable, boolean isMultiCurrency)
	{
		int i = 0;
		paymentTable.setColumnClass(i++, Boolean.class, false);         //  0-Selection
		paymentTable.setColumnClass(i++, Timestamp.class, true);        //  1-TrxDate
		paymentTable.setColumnClass(i++, String.class, true);           //  2-Value
		if (isMultiCurrency)
		{
			paymentTable.setColumnClass(i++, String.class, true);       //  3-Currency
			paymentTable.setColumnClass(i++, BigDecimal.class, true);   //  4-PayAmt
		}
		paymentTable.setColumnClass(i++, BigDecimal.class, true);       //  5-ConvAmt
		paymentTable.setColumnClass(i++, BigDecimal.class, true);       //  6-ConvOpen
		paymentTable.setColumnClass(i++, BigDecimal.class, false);      //  7-Allocated
		paymentTable.setColumnClass(i++, String.class, true);      		//  8-Contract
		
		//paymentTable.setColumnClass(i++, BigDecimal.class, true);      	//  9-Multiplier

		//
		i_payment = isMultiCurrency ? 7 : 5;
		
		//  Table UI
		paymentTable.autoSize();
	}
	
	/**************************************************************************
	 *  Action Listener.
	 *  - MultiCurrency
	 *  - Allocate
	 *  @param e event
	 */
	@Override
	public void actionPerformed(ActionEvent e)
	{
		Integer CMS_Contract_ID = (Integer)cms_Contract.getValue();
		Env.setContext(Env.getCtx(),m_WindowNo, "CMS_Contract_ID", ( CMS_Contract_ID != null ? CMS_Contract_ID : 0));
		boolean t = false; 
		for(Component c :cms_Contract.getComponents()) {
			t = t || e.getSource().equals(c);
		}
		if (t) {
			loadBPartner();
		} else {
			super.actionPerformed(e);
		}
	}   //  actionPerformed


}
